package account;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AccDAO implements IcDAO{
	private String DRV ="oracle.jdbc.driver.OracleDriver";
	private String URL ="jdbc:oracle:thin:spirit/spirit@112.216.253.125.1521:xe";
	private String insertSQL = "insert into account values(exam.nextval,?,?,?,sysdate";
	private String selectSQL = "select * from account order by accno asc";
	private String updateSQL = "update account set aid = ?, balance = ?, interestrate = ? where accno = ?";
	private String deleteSQL = "delete account where accno = ?";

	@Override
	public boolean insertaccount(Accountvo avo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, avo.getAid());
			pstmt.setString(2,avo.getBalance());
			pstmt.setString(3,avo.getInterestRate());
			pstmt.executeUpdate();
			result = true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}

	private Connection openDatebase() {
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	private void closeDatebase(
			Connection c, PreparedStatement p, ResultSet r){
		if(r != null)try{r.close(); r = null;}catch(Exception e){}
		if(p != null)try{p.close(); p = null;}catch(Exception e){}
		if(c != null)try{c.close(); c = null;}catch(Exception e){}
	}

	@Override
	public List<Accountvo> selectaccount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Accountvo>result = new ArrayList<>();
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Accountvo avo = new Accountvo();
				avo.setAid(rs.getString("aid"));
				avo.setBalance(rs.getString("balance"));
				avo.setInterestRate(rs.getString("interestrate"));
				
				result.add(avo);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			
		}finally{
		closeDatebase(conn,pstmt,rs);
		}
		return result;
	}

	@Override
	public boolean updateaccount(Accountvo ac) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deleteaccount(Accountvo ac) {
		// TODO Auto-generated method stub
		return false;
	}

}
